﻿

CREATE PROCEDURE [dbo].[fares_FiltreazaRaportFacturi] 
    @filtruPart varchar(8000),
    @filtruDoc varchar(8000),
	@data1 datetime,
	@data2 datetime,
	@datasold datetime,
	@facturiSold bit,
	@totalNeachitate bit,
	@integralAchitate bit,
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT * FROM 
	(SELECT     a.IDPartener, a.NrDoc, a.DataDoc, ROUND(a.Total, 2) AS Total, ROUND(a.TotalValoare, 2) AS TotalValoare, ROUND(a.TotalValoareTVA, 2) AS TotalValoareTVA, COALESCE (b.Valoare, 0) AS Valoare, ROUND(a.Total, 2) - COALESCE (b.Valoare, 0) AS RestPlata, 
               a.DataScadenta, dbo.Parteneri.Denumire, a.IDTipDocument, a.CodUnitate, cast(a.IDDocument AS Varchar(50)) AS IDDocument, a.CotaTVA
		FROM         dbo.Documente AS a LEFT OUTER JOIN
                          (SELECT     IDDocument, SUM(Valoare) AS Valoare
                            FROM          dbo.DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata=dp.IDDocumentPlata WHERE Data<=@datasold
                            GROUP BY IDDocument ) AS b ON a.IDDocument = b.IDDocument INNER JOIN
                      dbo.Parteneri ON a.IDPartener = dbo.Parteneri.IDPartener) a WHERE 
     (LEN(LTRIM(RTRIM(@filtruPart)))=0 OR (IDPartener IN (SELECT fart.value FROM dbo.SplitBigIntParameters(@filtruPart,',') fart))) AND           
     (LEN(LTRIM(RTRIM(@filtruDoc)))=0 OR (IDTipDocument IN (SELECT fart.value FROM dbo.SplitBigIntParameters(@filtruDoc,',') fart))) AND
	 CodUnitate = @CodUnitate AND [DataDoc]>=@data1 AND [DataDoc]<=@data2 AND
	 (@facturiSold=0 OR (@facturiSold=1 AND [RestPlata] <> 0)) AND
	 (@totalNeachitate=0 OR (@totalNeachitate=1 AND [RestPlata] = [Total])) AND 
	 (@integralAchitate=0 OR (@integralAchitate=1 AND  [RestPlata] = [Valoare]))
	 
	 ORDER BY [DataDoc]

END